﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace AVEIT.autogestionAVEIT.AccesoDatos
{
    public class AccesoADatos
    {
        protected static SqlConnection obtenerConexion()
        {
            SqlConnection con = new SqlConnection();
            try
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["AVEIT"].ConnectionString;
                con.Open();
            }
            catch (SqlException ex)
            {
                
                throw new ApplicationException("Error al establecer la conexion a la Base de Datos. " + ex.Message.ToString());
            }
            return con;
        }

        protected static void cerrarConexion(SqlConnection con)
        {
            if (con != null && con.State == ConnectionState.Open)
                con.Close();
        }

        public static void ejecutarSQL(String cadenaSQL, params SqlParameter[] parametros)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = obtenerConexion();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cadenaSQL;

                foreach (var item in parametros)
                {
                    cmd.Parameters.Add(item);
                }

                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                
                throw new ApplicationException("Error al ejecutar instruccion en la Base de Datos. " + ex.Message.ToString());
            }
            finally
            {
                cerrarConexion(obtenerConexion());
            }
        }

        public static int ejecutarSQLeIdentity(String cadenaSQLIdentity, params SqlParameter[] parametros)
        {
            
            int id = 0;
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = null;
            SqlConnection con = obtenerConexion();
            try
            {
                trans = con.BeginTransaction();
                cmd.Connection = con;
                cmd.Transaction = trans;

                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cadenaSQLIdentity;

                foreach (var item in parametros)
                {
                    cmd.Parameters.Add(item);
                }

                id = int.Parse(cmd.ExecuteScalar().ToString());
                trans.Commit();
            }
            catch (SqlException ex)
            {
                if (trans != null)
                    trans.Rollback();
                
                throw new ApplicationException("Error al ejecutar la transaccion en la Base de Datos. " + ex.Message.ToString());
            }
            finally
            {
                cerrarConexion(obtenerConexion());
            }
            return id;
        }

        public static void ejecutarSQLTransaccion(String cadenaSQLIdentity, params SqlParameter[] parametros)
        {
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = null;
            SqlConnection con = obtenerConexion();
            try
            {
                trans = con.BeginTransaction();
                cmd.Connection = con;
                cmd.Transaction = trans;

                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cadenaSQLIdentity;

                foreach (var item in parametros)
                {
                    cmd.Parameters.Add(item);
                }

                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlException ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw new ApplicationException("Error al ejecutar la transaccion en la Base de Datos ");
                throw new ApplicationException("Error al ejecutar la transaccion en la Base de Datos. " + ex.Message.ToString());
            }
            finally
            {
                cerrarConexion(obtenerConexion());
            }
        }

        public static DataTable consultarSQL(String cadenaSQL, params SqlParameter[] parametros)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            try
            {
                cmd.Connection = obtenerConexion();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cadenaSQL;

                foreach (var item in parametros)
                {
                    cmd.Parameters.Add(item);
                }
                dt.Load(cmd.ExecuteReader());
            }
            catch (SqlException ex)
            {
            }
            finally
            {
                cerrarConexion(obtenerConexion());
            }
            return dt;
        }

        public static String consultarSQLScalar(String cadenaSQL, params SqlParameter[] parametros)
        {
            SqlCommand cmd = new SqlCommand();
            String valorScalar = "";
            try
            {
                cmd.Connection = obtenerConexion();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cadenaSQL;

                foreach (var item in parametros)
                {
                    cmd.Parameters.Add(item);
                }
                valorScalar = cmd.ExecuteScalar().ToString();
            }
            catch (SqlException ex)
            {
                
                throw new ApplicationException("Error al consultar la Base de Datos. " + ex.Message.ToString());
            }
            finally
            {
                cerrarConexion(obtenerConexion());
            }
            return valorScalar;
        }

        public static SqlDataReader consultarSqlDReader(String cadenaSQL, params SqlParameter[] parametros)
        {
            SqlCommand cmd = new SqlCommand();
            
            try
            {
                cmd.Connection = obtenerConexion();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cadenaSQL;
                SqlDataReader dr = cmd.ExecuteReader();
                return dr;
            }
            catch (SqlException ex)
            {
                ex.Message.ToString();
            }
            finally
            {
                cerrarConexion(obtenerConexion());
            }
            return null;
        }
    }
}